import pandas as pd


def extract_sheets(file_path1):
    # 读取Excel文件中的所有工作表
    xlsx = pd.ExcelFile(file_path1)
    sheet_names = xlsx.sheet_names

    # 创建一个字典表来存储每个工作表的提取结果
    extracted_data = {}

    for sheet_name in sheet_names:
        # 读取单个工作表
        df = xlsx.parse(sheet_name)

        # 提取第10行到第59行的第14列、第35列、第85列、第92列、第94列、第96列、第98列
        rows_10_to_59 = df.iloc[9:59, [13, 34, 84, 91, 93, 95, 97]]

        # 提取第39行到第59行的第106列、第108列、第110列
        rows_39_to_59 = df.iloc[38:59, [105, 107, 109]]

        # 提取第63行、第64行的第21列和第50列
        rows_63_to_64 = df.iloc[[61, 62], [20, 49]]

        # 将提取的数据合并到一个新的DataFrame中
        result = pd.concat([rows_10_to_59, rows_39_to_59, rows_63_to_64], axis=1)

        # 生成唯一的列名
        empty_column_counter = 1


        def get_unique_column_name():
            nonlocal empty_column_counter
            column_name = f'Empty{empty_column_counter}'
            while column_name in result.columns:
                empty_column_counter += 1
                column_name = f'Empty{empty_column_counter}'
            return column_name

        # 在第一列的后面加6列空列
        for _ in range(6):
            column_name = get_unique_column_name()
            result.insert(1, column_name, '')

        # 在第二列的后面加一列空列
        column_name = get_unique_column_name()
        result.insert(8, column_name, '')

        # 在第三列的后面加两列空列
        for _ in range(2):
            column_name = get_unique_column_name()
            result.insert(10 + _, column_name, '')

        # 在第四列的后面加两列空列
        for _ in range(2):
            column_name = get_unique_column_name()
            result.insert(13 + _, column_name, '')

        # 在第11列的后面加两列空列
        for _ in range(2):
            column_name = get_unique_column_name()
            result.insert(22 + _, column_name, '')

        # 将第一行中所有包含 'Empty' 和 'Unnamed' 的单元格替换为空
        result.columns = [col if not (col.startswith('Empty') or col.startswith('Unnamed')) else '' for col in result.columns]

        # 存储结果
        extracted_data[sheet_name] = result

    # 将所有提取的结果保存到一个新的Excel文件
    with pd.ExcelWriter('extracted_data.xlsx') as writer:
        for sheet_name, result in extracted_data.items():
            result.to_excel(writer, sheet_name=sheet_name, index=False)


def process_sheets(file_path2, output_path2):
    # 读取Excel文件的所有sheet
    excel_file = pd.ExcelFile(file_path2)
    sheets = excel_file.sheet_names

    # 创建一个字典表来存储结果
    result_dict = {}

    for sheet in sheets:
        # 读取当前sheet
        df = pd.read_excel(excel_file, sheet_name=sheet)
        # print(f"Sheet: {sheet}, Shape: {df.shape}")

        # 提取第52行25列的值
        wj_value = df.iloc[50, 24]  # 注意索引从0开始
        panel_value = df.iloc[51, 24]

        # print(f"Sheet: {sheet}, WJ Value: {wj_value}, Panel Value: {panel_value}")

        # 获取sheet名称的前缀
        prefix = sheet.split('-')[0]

        # 如果前缀不在结果字典中，则添加
        if prefix not in result_dict:
            result_dict[prefix] = {'WJ': set(), 'Panel': set()}

        # 添加WJ和Panel值到集合中去重
        if pd.notna(wj_value) and isinstance(wj_value, str) and wj_value.startswith('WJ'):
            result_dict[prefix]['WJ'].add(wj_value)
            # print(f"Added WJ value: {wj_value} for prefix: {prefix}")

        if pd.notna(panel_value):
            result_dict[prefix]['Panel'].add(panel_value)
            # print(f"Added Panel value: {panel_value} for prefix: {prefix}")

    # 打印 result_dict 以调试
    # print("Result dictionary:")
    # for prefix, values in result_dict.items():
    #     print(f"Prefix: {prefix}, WJ: {values['WJ']}, Panel: {values['Panel']}")

    # 将结果转换为DataFrame
    result_df = pd.DataFrame(columns=['Prefix', 'Panel', 'WJ'])

    for prefix, values in result_dict.items():
        for wj in values['WJ']:
            for panel in values['Panel']:
                # 使用 pd.concat 来追加数据
                new_row = pd.DataFrame({'Prefix': [prefix], 'Panel': [panel], 'WJ': [wj]})
                result_df = pd.concat([result_df, new_row], ignore_index=True)

    # # 打印最终的 DataFrame 以调试
    # print("Final result_df:")
    # print(result_df)

    # 写入新的Excel文件
    result_df.to_excel(output_path2, index=False)



def IOM_Processing(file_path3, output_path3):
    # 读取Excel文件的所有sheet
    excel_file = pd.ExcelFile(file_path3)
    sheets = excel_file.sheet_names

    # 创建一个空的DataFrame来存储所有提取的数据
    result_df = pd.DataFrame(columns=['Sheet Name', 'IOM'])

    for sheet in sheets:
        # 读取当前sheet
        df = pd.read_excel(excel_file, sheet_name=sheet)

        # 提取第31到第50行的第21列的数据
        iom_values = df.iloc[29:49, 20].dropna()  # 注意索引从0开始

        # 创建一个新的DataFrame，包含sheet名称和IOM值
        sheet_name = [sheet] * len(iom_values)
        temp_df = pd.DataFrame({'Sheet Name': sheet_name, 'IOM': iom_values})

        # 将当前sheet的数据追加到结果DataFrame中
        result_df = pd.concat([result_df, temp_df], ignore_index=True)

    # 提取Sheet Name中-之前的数字部分
    result_df['Sheet Group'] = result_df['Sheet Name'].apply(lambda x: x.split('-')[0])

    # 计算每个Sheet Group下每个IOM值出现的次数
    grouped_df = result_df.groupby(['Sheet Group', 'IOM']).size().reset_index(name='Count')

    # 写入新的Excel文件
    grouped_df.to_excel(output_path3, index=False)


def Point_Processing(file_path4, output_path4):
    # 读取Excel文件的所有sheet
    excel_file = pd.ExcelFile(file_path4)
    sheets = excel_file.sheet_names

    # 创建一个字典来存储每个sheet的提取数据
    data_dict = {}

    for sheet in sheets:
        # 读取当前sheet
        df = pd.read_excel(excel_file, sheet_name=sheet)

        # 提取第1、9、11列的第10行到第59行的数据
        extracted_data_1 = df.iloc[0:51, 0]  # 注意：iloc索引从0开始
        extracted_data_2 = df.iloc[0:51, 7]
        extracted_data_3 = df.iloc[0:51, 9]
        extracted_data_4 = df.iloc[0:51, 16]


        # 将提取的数据合并到一个DataFrame中
        combined_data = pd.DataFrame({
            'I/O Object Name': extracted_data_1,
            'Object Type': extracted_data_2,
            'I/O Type': extracted_data_3,
            'I/O Assignment': extracted_data_4
        })

        # 将合并的数据存储到字典中
        data_dict[sheet] = combined_data

    # 将字典中的数据写入新的Excel文件，每个sheet对应一个提取结果
    with pd.ExcelWriter(output_path4) as writer:
        for sheet, data in data_dict.items():
            data.to_excel(writer, sheet_name=sheet, index=False)




def count_object_types(file_path5, output_path5):
    # 读取Excel文件的所有sheet
    excel_file = pd.ExcelFile(file_path5)
    sheets = excel_file.sheet_names

    # 创建一个空的DataFrame来存储所有sheet的统计结果
    all_results = pd.DataFrame(columns=['Sheet Name', 'Object Type', 'Count', 'Used Count'])

    for sheet in sheets:
        # 读取当前sheet
        df = pd.read_excel(excel_file, sheet_name=sheet)

        # 排除'I/O Assignment'为空的情况
        df = df.dropna(subset=['I/O Assignment'])

        # 处理Momentary的情况
        df['Count Multiplier'] = df.apply(lambda row: 2 if row['I/O Type'] == 'Momentary' else 1, axis=1)

        # 统计"Object Type"列中每个值的出现次数
        object_type_counts = df.groupby('Object Type')['Count Multiplier'].sum().reset_index()
        object_type_counts.columns = ['Object Type', 'Count']

        # 计算已使用的count数，排除"I/O Object Name"为"备用"的情况
        used_df = df[df['I/O Object Name'] != '备用']
        used_count = used_df.groupby('Object Type')['Count Multiplier'].sum().reset_index()
        used_count.columns = ['Object Type', 'Used Count']

        # 合并count和used count
        result = object_type_counts.merge(used_count, on='Object Type', how='left').fillna(0)

        # 添加sheet名称列
        result['Sheet Name'] = sheet

        # 重新排列列的顺序
        result = result[['Sheet Name', 'Object Type', 'Count', 'Used Count']]

        # 将当前sheet的统计结果追加到all_results中
        all_results = pd.concat([all_results, result], ignore_index=True)

    # 提取Sheet Name中-之前的数字部分
    all_results['Sheet Group'] = all_results['Sheet Name'].apply(lambda x: x.split('-')[0])

    # 按Sheet Group和Object Type分组，计算每个Object Type的总count和used count
    grouped_results = all_results.groupby(['Sheet Group', 'Object Type']).agg({'Count': 'sum', 'Used Count': 'sum'}).reset_index()

    # 重命名Sheet Group为Sheet Name
    grouped_results.rename(columns={'Sheet Group': 'Sheet Name'}, inplace=True)

    # 新增一列：Reserved Count
    grouped_results['Reserved Count'] = grouped_results['Count'] - grouped_results['Used Count']

    # 重新排列列的顺序
    grouped_results = grouped_results[['Sheet Name', 'Object Type', 'Count', 'Used Count', 'Reserved Count']]

    # 将合并后的结果写入新的Excel文件
    grouped_results.to_excel(output_path5, index=False)


import pandas as pd

def add_columns_and_fill_values(panel_wj_path, object_type_counts_path, output_path):
    # 读取Panel_WJ_data.xlsx
    panel_wj_df = pd.read_excel(panel_wj_path)

    # 读取Object_Type_Counts.xlsx
    object_type_counts_df = pd.read_excel(object_type_counts_path)

    # 新增列
    new_columns = ['AI', 'AI(PT)', 'AI(PTK)', 'AO', 'AO(MM)', 'BI', 'BO', 'AC']
    for col in new_columns:
        panel_wj_df[col] = 0

    # 填充新列的值
    for index, row in panel_wj_df.iterrows():
        prefix = row['Prefix']
        for col in new_columns:
            count = object_type_counts_df[
                (object_type_counts_df['Sheet Name'] == prefix) &
                (object_type_counts_df['Object Type'] == col)
            ]['Count'].values
            if count.size > 0:
                panel_wj_df.at[index, col] = count[0]

    # 保存结果到新的Excel文件
    panel_wj_df.to_excel(output_path, index=False)

# 文件路径
panel_wj_path = 'Panel_WJ_data.xlsx'
object_type_counts_path = 'Object_Type_Counts.xlsx'
output_path = 'Updated_Panel_WJ_data.xlsx'

# 调用函数
add_columns_and_fill_values(panel_wj_path, object_type_counts_path, output_path)



file_path1 = "IO Object List (Detail).xlsx"
extract_sheets(file_path1)
# 盘和WJ
file_path2 = 'extracted_data.xlsx'
output_path2 = 'Panel_WJ_data.xlsx'
process_sheets(file_path2, output_path2)
# IO模块
file_path3 = 'extracted_data.xlsx'
output_path3 = 'IOM_data.xlsx'
IOM_Processing(file_path3, output_path3)
# 点位提取与计算
file_path4 = 'extracted_data.xlsx'
output_path4 = 'Point_data.xlsx'
Point_Processing(file_path4, output_path4)

file_path5 = 'Point_data.xlsx'
output_path5 = 'Object_Type_Counts.xlsx'
count_object_types(file_path5, output_path5)


import pandas as pd
from openpyxl import Workbook
from openpyxl.utils.dataframe import dataframe_to_rows
from openpyxl.styles import Alignment

def merge_data(panel_wj_path, iom_path, output_path):
    # 读取Panel-WJ数据
    panel_wj_df = pd.read_excel(panel_wj_path)

    # 读取IOM数据
    iom_df = pd.read_excel(iom_path)

    # 去重
    panel_wj_df.drop_duplicates(inplace=True)
    iom_df.drop_duplicates(inplace=True)

    # 合并Panel-WJ和IOM数据
    merged_df = pd.merge(panel_wj_df, iom_df, left_on='Prefix', right_on='Sheet Group', how='left')

    # 添加IOM_Count列
    merged_df['IOM_Count'] = merged_df['Count']

    # 检查列是否存在
    required_columns = ['Prefix', 'Panel', 'WJ', 'IOM', 'IOM_Count']
    missing_columns = [col for col in required_columns if col not in merged_df.columns]
    if missing_columns:
        raise KeyError(f"缺失的列: {missing_columns}")

    # 选择需要的列并调整顺序
    merged_df = merged_df[required_columns]

    # 去除重复的记录
    merged_df.drop_duplicates(inplace=True)

    # 创建一个新的工作簿
    wb = Workbook()
    ws = wb.active

    # 写入标题行
    ws.append(required_columns)

    # 将DataFrame写入工作表
    last_panel_value = None
    row_offset = 1  # 用于记录插入空行后的行偏移量，初始值为1（标题行）
    for idx, row in merged_df.iterrows():
        current_panel_value = row['Panel']
        if current_panel_value != last_panel_value and idx > 0:
            ws.insert_rows(idx + 2 + row_offset)  # 插入空行
            row_offset += 1
            # 填充空行的所有单元格为空字符串
            for col in range(1, len(required_columns) + 1):
                ws.cell(row=idx + 2 + row_offset - 1, column=col).value = ''
        ws.append(row.tolist())
        last_panel_value = current_panel_value

    # 设置单元格的对齐方式
    for col in ['A', 'B', 'C', 'D', 'E']:
        for cell in ws[col]:
            cell.alignment = Alignment(horizontal='center', vertical='center')

    # 在第一行的第F列之后的单元格填充AI、AI(PT)、AI(PTK)、AO、AO(MM)、BI、BO、AC，填充三次
    fill_values = ['AI', 'AI(PT)', 'AI(PTK)', 'AO', 'AO(MM)', 'BI', 'BO', 'AC']
    for i in range(3):
        for j, value in enumerate(fill_values, start=6):  # 从第F列开始，即索引为6
            ws.cell(row=1, column=j + i * len(fill_values)).value = value

    # 保存工作簿
    wb.save(output_path)

# 文件路径
panel_wj_path = 'Panel_WJ_data.xlsx'
iom_path = 'IOM_data.xlsx'
output_path = 'Merged_Data.xlsx'

# 调用函数
merge_data(panel_wj_path, iom_path, output_path)
